package hoowin.basic.dao.springImp;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import hoowin.basic.dao.IAccountDao;
import hoowin.basic.model.Account;
import hoowin.basic.util.Trans;

import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.core.RowMapper;

public class AccountDao extends JdbcDaoSupport implements IAccountDao {

	@Override
	public Account getAccountById(int id) {
		String sql = "select * from basic_accounts where id="+id;
		List<Object> items = super.getJdbcTemplate().query(sql, new AccountRowMapper());
		if(items.isEmpty()||items.size()==0){  
			   return null;  
		}  
		return (Account)items.get(0);
	}

	@Override
	public Account getAccountByEmail(String email) {
		String sql = "select * from basic_accounts where email='"+email+"'";
		List<Object> items = super.getJdbcTemplate().query(sql, new AccountRowMapper());
		if(items.isEmpty()||items.size()==0){  
			   return null;  
		}  
		return (Account)items.get(0);
	}
	
	class AccountRowMapper implements RowMapper<Object>{
		@Override
		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			Account account = new Account();
			account.setId(rs.getInt("id"));
			account.setEmail(rs.getString("email"));
			account.setPhone(rs.getString("phone"));
			account.setPassword(rs.getString("password"));
			return account;
		}	
	}

	@Override
	public Account getAccountByPhone(String phone) {
		String sql = "select * from basic_accounts where phone='"+phone+"'";
		List<Object> items = super.getJdbcTemplate().query(sql, new AccountRowMapper());
		if(items.isEmpty()||items.size()==0){  
			   return null;  
		}  
		return (Account)items.get(0);
	}

	@Override
	public int getInvitorByCode(String code) {
		String sql = "select invitor from basic_invitations where code='"+code+"'";
		return super.getJdbcTemplate().queryForInt(sql);
	}

	@Override
	public boolean saveInvitation(String code, int invitorId) {
		String sql = "insert into basic_invitations (code, invitor, invite_time) values (?, ?, ?)";
		return super.getJdbcTemplate().update(sql, code, invitorId, Trans.dateToString(new Date()))>0;
	}

	@Override
	public boolean deleteInvitation(String code) {
		return super.getJdbcTemplate().update("delete from basic_invitations where code='"+code+"'")>0;
	}

	@Override
	public int addAccount(Account account) {
		String sql = "insert into basic_accounts (id, email, password, phone) values (null, ?, ?, ?)";
		if(super.getJdbcTemplate().update(sql, account.getEmail(), account.getPassword(), account.getPhone())>0){
			return super.getJdbcTemplate().queryForInt("select last_insert_id()");
		}else return 0;
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	@Override
	public List getRemainEmails() {
		String sql="SELECT distinct(email) FROM basic_emails where email not in (select email from basic_accounts) and id>37"; 
		return super.getJdbcTemplate().queryForList(sql, String.class);
	}
}
